package com.ph.irp.sell.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.model.PSellSellprice;
import com.ph.irp.sell.dao.SellPriceDao;
import com.ph.util.DBUtil;

public class SellPriceDaoImpl implements SellPriceDao{
	
	/** The data source. */
	private ComboPooledDataSource dataSource;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * 新增
	 */
	
	public void addSellPrice(PSellSellprice sellprice)throws Throwable{
		
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("insert into P_sell_sellprice (departmentId,businesserId,currencyId,customerId,managerId,sellPriceCode,sellDate,exchangeRate,makeBiller,checkDate,checker,checkState,blankOutState) values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pStmt.setInt(1, sellprice.getDepartmentId());
			pStmt.setInt(2, sellprice.getBusinesserId());
			pStmt.setInt(3, sellprice.getCurrencyId());
			pStmt.setInt(4, sellprice.getCustomerId());
			pStmt.setInt(5, sellprice.getManagerId());
			pStmt.setString(6, sellprice.getSellPriceCode());
			pStmt.setString(7, sellprice.getSellDate());
			pStmt.setFloat(8, sellprice.getExchangeRate());
			pStmt.setString(9, sellprice.getMakeBiller());
			pStmt.setString(10, sellprice.getCheckDate());
			pStmt.setString(10, sellprice.getCheckDate());
			pStmt.setString(11, sellprice.getChecker());
			pStmt.setInt(12, sellprice.getCheckState());
			pStmt.setInt(13, sellprice.getBlankOutState());
			
			pStmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}


	}
	/**
	 * 删除
	 * 
	 * @param id
	 * @return null;
	 * @throws SQLException
	 */
	public void deleteSellPrice(int id) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("delete from P_sell_sellprice where sellPriceId = ?");
			pStmt.setInt(1, id);

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}

	}
	
	
	
	
	/**
	 * 查询全部
	 */
	
	public List<PSellSellprice> getAllSellPrice()throws Throwable{
		
		List<PSellSellprice> sellPriceList = new ArrayList<PSellSellprice>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_sell_sellprice");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(sellPriceList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return sellPriceList;
	}
	/**
	 * 查询ById ,但是返回List<PSellSellprice>
	 */
	
	public List<PSellSellprice> getSellPriceListById(int sellPriceId)throws Throwable{
		
		List<PSellSellprice> sellPriceList = new ArrayList<PSellSellprice>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from p_sell_sellprice where sellPriceId=?");
			// 给宿主变量绑定值
			statement.setInt(1, sellPriceId);
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(sellPriceList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return sellPriceList;
	}
	
	
	/**
	 * 查询ByID
	 */
	
	public PSellSellprice getSellPriceById(int sellPriceId)throws Throwable{
		
		List<PSellSellprice> sellPriceList = new ArrayList<PSellSellprice>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from p_sell_sellprice where sellPriceId=?");
			// 给宿主变量绑定值
			statement.setInt(1, sellPriceId);
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(sellPriceList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return sellPriceList.get(0);
	}
	/**
	 * 查询ByConditon
	 */
	public List<PSellSellprice> getSellPriceByCondition(PSellSellprice sellprice)throws Throwable{
		
		List<PSellSellprice> sellPriceList = new ArrayList<PSellSellprice>();
		
		//数据库连接
		Connection con = null ;
		//预编译语句
		PreparedStatement ps = null ;
		//结果集
		ResultSet rs = null ;
		//构建查询语句
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_sell_sellprice where 1=1");
		
		if(sellprice!=null){
			if(sellprice.getCustomerId() != 0){
				sb.append(" and customerId like ");
				sb.append(sellprice.getCustomerId());
			}
			if(sellprice.getSellPriceCode() != null && !sellprice.getSellPriceCode().equals("")){
				sb.append(" and sellPriceCode like '%");
				sb.append(sellprice.getSellPriceCode());
				sb.append("%'");
			}
			if(sellprice.getSellDate() != null && !sellprice.getSellDate().equals("")){
				
				String strDate = sellprice.getSellDate();
				SimpleDateFormat sDateFormat = new SimpleDateFormat("yyyy-MM-dd");
				java.util.Date uDate = sDateFormat.parse(strDate);
				java.sql.Date dateFrom = new java.sql.Date(uDate.getTime());
				
				String str2Date = sellprice.getCheckDate();
				SimpleDateFormat s2DateFormat = new SimpleDateFormat("yyyy-MM-dd");
				java.util.Date u2Date = s2DateFormat.parse(str2Date);
				java.sql.Date dateTo = new java.sql.Date(u2Date.getTime());
				
				sb.append(" and sellDate between '");
				sb.append(dateFrom);
				sb.append("'");
				sb.append(" and '");
				sb.append(dateTo);
				sb.append("'");
			}
			if(sellprice.getCheckState() != null){
				if(sellprice.getCheckState() != 2){
					sb.append(" and checkState = ");
					sb.append(sellprice.getCheckState());
				}
				
			}
	
		}
		try {
			// 由数据库获得连接
			con = dataSource.getConnection();
			ps = con.prepareStatement(sb.toString());
			// 执行预备语句，得到结果集
			rs = ps.executeQuery();
			turnResultSetToList(sellPriceList, rs);
			
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, ps, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return sellPriceList;
		
	}
	
	/**
	 * 查询IDbyCode
	 */
	public int getSellPriceId(String code) throws Throwable{
		
		int sellPriceId = 0 ;
		//数据库连接
		Connection con = null ;
		//预编译语句
		PreparedStatement ps = null ;
		//结果集
		ResultSet rs = null ;
		
		//构建查询语句
		StringBuilder sql = new StringBuilder();
		sql.append("select SellPriceID from p_sell_sellprice where SellPriceCode='") ;
		sql.append(code) ;
		sql.append("'") ;
		try {
			//获得数据库连接
			con = dataSource.getConnection() ;
			//得到预编译语句
			ps = con.prepareStatement(sql.toString()) ;
			//得到结果集
			rs = ps.executeQuery() ;
			rs.next() ;
			
			sellPriceId = rs.getInt(1) ;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage()) ;
		}finally{
			try {
				DBUtil.setNullObject(con, ps, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw e ;
			}
		}

		return sellPriceId;
	}
	/**
	 * 得到最新的编码
	 */
	public String getNewestSellPriceCode() throws Throwable {
		String res = null;
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("select sellPriceCode from p_sell_sellPrice ");
		sb
				.append("where sellPriceId=(select max(sellPriceId) from p_sell_sellPrice)");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getString("sellPriceCode");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return res;

	}

	
	
	
	/**
	 * 迭代结果集，将数据封装到sellPriceList中
	 * 
	 * @param settlementList
	 * @param resultSet
	 * @return
	 * @throws Throwable
	 */
	public List<PSellSellprice> turnResultSetToList(
			List<PSellSellprice> sellPriceList, ResultSet resultSet)
			throws Throwable {

		// 迭代结果集
		while (resultSet.next()) {

			PSellSellprice sellprice = new PSellSellprice();

			sellprice.setBlankOutState(resultSet.getInt("blankOutState"));
			sellprice.setBusinesserId(resultSet.getInt("businesserId"));
			sellprice.setCheckDate(resultSet.getString("checkDate"));
			sellprice.setChecker(resultSet.getString("checker"));
			sellprice.setCheckState(resultSet.getInt("checkState"));
			sellprice.setCurrencyId(resultSet.getInt("currencyId"));
			sellprice.setCustomerId(resultSet.getInt("customerId"));
			sellprice.setDepartmentId(resultSet.getInt("departmentId"));
			sellprice.setExchangeRate(resultSet.getFloat("exchangeRate"));
			sellprice.setMakeBiller(resultSet.getString("makeBiller"));
			sellprice.setManagerId(resultSet.getInt("managerId"));
			sellprice.setSellDate(resultSet.getString("sellDate"));
			sellprice.setSellPriceCode(resultSet.getString("sellPriceCode"));
			sellprice.setSellPriceId(resultSet.getInt("sellPriceId"));
			
			sellPriceList.add(sellprice);
		}

		return sellPriceList;
	}
	
}
